from flask import Blueprint, render_template, request
from datetime import datetime
from database import get_db
from flask import make_response
import pandas as pd
from io import BytesIO
from reportlab.lib import colors
from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.ttfonts import TTFont

report_annual_emission_bp = Blueprint('report_annual_emission', __name__, url_prefix='/report/annual-emission')

@report_annual_emission_bp.route('/')
def index():
    selected_year = request.args.get('year', default=datetime.now().year, type=int)
    organization_boundary = request.args.get('organization_boundary')
    report_data = get_report_data(selected_year, organization_boundary)
    return render_template('report_annual_emission.html', 
                         report_data=report_data,
                         selected_year=selected_year,
                         selected_organization=organization_boundary)


def get_report_data(year, organization_boundary=None):
    db = get_db()
    
    # 构建查询条件
    query_params = [year]
    where_clauses = ['ci.belong_year = ?']
    
    # 添加组织边界筛选条件
    if organization_boundary:
        where_clauses.append('ci.organization_boundary = ?')
        query_params.append(organization_boundary)
    
    # 查询化石燃料排放源数据
    fossil_fuel_sql = f'''
        SELECT SUM(ies.activity_data * cf.emission_value) as fossil_fuel_emission,
               SUM(ies.activity_data) as fossil_fuel_activity
        FROM inventory_emission_sources ies
        JOIN carbon_inventory ci ON ies.inventory_id = ci.id
        JOIN models m ON ies.emission_source_id = m.id
        JOIN carbon_factors cf ON m.factor_id = cf.id
        JOIN model_categories mc ON ci.model_categories_id = mc.id
        WHERE {' AND '.join(where_clauses)}
        AND mc.name = '化石燃料排放'
    '''
    
    fossil_fuel_result = db.execute(fossil_fuel_sql, query_params).fetchone()
    
    # 先删除表中该年份的旧数据
    db.execute("DELETE FROM report_annual_emission WHERE year = ?", (year,))
    
    # 插入新数据
    db.execute(
        """INSERT INTO report_annual_emission (
            year, fossil_fuel_co2_t, fossil_fuel_co2_tco2e
        ) VALUES (?, ?, ?)""",
        (
            year,
            fossil_fuel_result['fossil_fuel_activity'],  # fossil_fuel_co2_t
            fossil_fuel_result['fossil_fuel_emission'],  # fossil_fuel_co2_tco2e    
        )
    )
    db.commit()
    
    # 新增购入电力排放查询
    electricity_sql = f'''
        SELECT SUM(ies.activity_data * cf.emission_value) as electricity_emission,
               SUM(ies.activity_data) as electricity_activity
        FROM inventory_emission_sources ies
        JOIN carbon_inventory ci ON ies.inventory_id = ci.id
        JOIN models m ON ies.emission_source_id = m.id
        JOIN carbon_factors cf ON m.factor_id = cf.id
        JOIN model_categories mc ON ci.model_categories_id = mc.id
        WHERE {' AND '.join(where_clauses)}
        AND mc.name = '电力排放'
    '''

    electricity_result = db.execute(electricity_sql, query_params).fetchone()
    
    # 更新电力排放字段
    db.execute(
        """UPDATE report_annual_emission 
        SET purchased_electricity_t = ?, 
            purchased_electricity_tco2e = ?
        WHERE year = ?""",
        (electricity_result['electricity_activity'],
         electricity_result['electricity_emission'],
         year)
    )
    db.commit()
    # 查询企业温室气体排放总量(不包括购入和输出的电力、热力)
    total_emission_exclude_energy_tco2e_sql = f'''
        SELECT SUM(ies.activity_data * cf.emission_value) as total_emission_exclude_energy_tco2e
        FROM inventory_emission_sources ies
        JOIN carbon_inventory ci ON ies.inventory_id = ci.id
        JOIN models m ON ies.emission_source_id = m.id
        JOIN carbon_factors cf ON m.factor_id = cf.id
        JOIN model_categories mc ON ci.model_categories_id = mc.id
        WHERE {' AND '.join(where_clauses)}
        AND mc.name = '化石燃料排放'
    '''
    
    total_emission_exclude_energy_tco2e_result = db.execute(total_emission_exclude_energy_tco2e_sql, query_params).fetchone()
    
    # 插入新数据
    db.execute(
        """UPDATE report_annual_emission 
        SET 
            total_emission_exclude_energy_tco2e = ?
        WHERE year = ?""",
        (total_emission_exclude_energy_tco2e_result['total_emission_exclude_energy_tco2e'],
         year)
    ) 
    db.commit()

    # 查询企业温室气体排放总量(包括购入和输出的电力、热力)
    total_emission_include_energy_tco2e_sql = f'''
        SELECT SUM(ies.activity_data * cf.emission_value) as total_emission_include_energy_tco2e
        FROM inventory_emission_sources ies
        JOIN carbon_inventory ci ON ies.inventory_id = ci.id
        JOIN models m ON ies.emission_source_id = m.id
        JOIN carbon_factors cf ON m.factor_id = cf.id
        JOIN model_categories mc ON ci.model_categories_id = mc.id
        WHERE {' AND '.join(where_clauses)}
        AND mc.name IN ('电力排放', '化石燃料排放')
    '''
    
    total_emission_include_energy_tco2e_result = db.execute(total_emission_include_energy_tco2e_sql, query_params).fetchone()
    
    # 插入新数据
    db.execute(
        """UPDATE report_annual_emission 
        SET 
            total_emission_include_energy_tco2e = ?
        WHERE year = ?""",
        (total_emission_include_energy_tco2e_result['total_emission_include_energy_tco2e'],
         year)
    ) 
    db.commit()

    return {
        'year': year,
        'organization_boundary': organization_boundary or '所有',
        'fossil_fuel_t': fossil_fuel_result['fossil_fuel_activity'] if fossil_fuel_result['fossil_fuel_activity'] else 0,
        'fossil_fuel_tco2e': fossil_fuel_result['fossil_fuel_emission'] if fossil_fuel_result['fossil_fuel_emission'] else 0,
        'purchased_electricity_t': electricity_result['electricity_activity'] if electricity_result['electricity_activity'] else 0,
        'purchased_electricity_tco2e': electricity_result['electricity_emission'] if electricity_result['electricity_emission'] else 0,
        'total_emission_exclude_energy_tco2e':  total_emission_exclude_energy_tco2e_result['total_emission_exclude_energy_tco2e'] if total_emission_exclude_energy_tco2e_result['total_emission_exclude_energy_tco2e'] else 0,
        'total_emission_include_energy_tco2e':  total_emission_include_energy_tco2e_result['total_emission_include_energy_tco2e'] if total_emission_include_energy_tco2e_result['total_emission_include_energy_tco2e'] else 0
    }

@report_annual_emission_bp.route('/export/<format>')
def export(format):
    selected_year = request.args.get('year', default=datetime.now().year, type=int)
    report_data = get_report_data(selected_year)
    
    if format == 'excel':
        output = BytesIO()
        writer = pd.ExcelWriter(output, engine='xlsxwriter')
        
        # 创建数据框
        data = {
            '源类别': [
                '化石燃料燃烧CO2排放',
                'CO2过程排放',
                'HFCs过程排放',
                'PFCs过程排放',
                'SF6过程排放',
                '购入电力产生的排放量',
                '购入热力产生的排放量',
                '输出电力产生的排放量',
                '输出热力产生的排放量',
                '企业温室气体排放总量(不包括购入和输出的电力、热力)',
                '企业温室气体排放总量(包括购入和输出的电力、热力)'
            ],
            '排放量(t)': [
                report_data.get('fossil_fuel_t', ''),
                report_data.get('process_co2_t', ''),
                report_data.get('process_hfcs_t', ''),
                report_data.get('process_pfcs_t', ''),
                report_data.get('process_sf6_t', ''),
                report_data.get('purchased_electricity_t', ''),
                report_data.get('purchased_heat_t', ''),
                report_data.get('exported_electricity_t', ''),
                report_data.get('exported_heat_t', ''),
                report_data.get('total_without_energy_t', ''),
                report_data.get('total_with_energy_t', '')
            ],
            '排放量(tCO2e)': [
                report_data.get('fossil_fuel_tco2e', ''),
                report_data.get('process_co2_tco2e', ''),
                report_data.get('process_hfcs_tco2e', ''),
                report_data.get('process_pfcs_tco2e', ''),
                report_data.get('process_sf6_tco2e', ''),
                report_data.get('purchased_electricity_tco2e', ''),
                report_data.get('purchased_heat_tco2e', ''),
                report_data.get('exported_electricity_tco2e', ''),
                report_data.get('exported_heat_tco2e', ''),
                report_data.get('total_without_energy_tco2e', ''),
                report_data.get('total_with_energy_tco2e', '')
            ]
        }
        
        df = pd.DataFrame(data)
        df.to_excel(writer, sheet_name=f'{selected_year}年度排放报表', index=False)
        writer.close()
        output.seek(0)
        response = make_response(output.getvalue())
        response.headers['Content-Type'] = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        response.headers['Content-Disposition'] = f'attachment; filename=annual_emission_report_{selected_year}.xlsx'
        return response
    
    elif format == 'pdf':
        from reportlab.pdfgen import canvas
        from reportlab.lib.pagesizes import letter
        from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph
        from reportlab.lib.styles import getSampleStyleSheet
        
        pdfmetrics.registerFont(TTFont('SimSun', 'simsun.ttc'))
        
        output = BytesIO()
        doc = SimpleDocTemplate(output, pagesize=letter)
        
        data = [
            ['源类别', '排放量(t)', '排放量(tCO2e)']
        ]
        
        # 添加数据行
        categories = [
            ('化石燃料燃烧CO2排放', 'fossil_fuel_t', 'fossil_fuel_tco2e'),
            ('CO2过程排放', 'process_co2_t', 'process_co2_tco2e'),
            ('HFCs过程排放', 'process_hfcs_t', 'process_hfcs_tco2e'),
            ('PFCs过程排放', 'process_pfcs_t', 'process_pfcs_tco2e'),
            ('SF6过程排放', 'process_sf6_t', 'process_sf6_tco2e'),
            ('购入电力产生的排放量', 'purchased_electricity_t', 'purchased_electricity_tco2e'),
            ('购入热力产生的排放量', 'purchased_heat_t', 'purchased_heat_tco2e'),
            ('输出电力产生的排放量', 'exported_electricity_t', 'exported_electricity_tco2e'),
            ('输出热力产生的排放量', 'exported_heat_t', 'exported_heat_tco2e'),
            ('企业温室气体排放总量(不包括购入和输出的电力、热力)', 'total_without_energy_t', 'total_without_energy_tco2e'),
            ('企业温室气体排放总量(包括购入和输出的电力、热力)', 'total_with_energy_t', 'total_with_energy_tco2e')
        ]
        
        for category, t_key, tco2e_key in categories:
            data.append([
                category,
                report_data.get(t_key, ''),
                report_data.get(tco2e_key, '')
            ])
        
        styles = getSampleStyleSheet()
        styles['Title'].fontName = 'SimSun'
        title = Paragraph(f'{selected_year}年度温室气体排放量汇总表', styles['Title'])
        
        table = Table(data)
        table.setStyle(TableStyle([
            ('BACKGROUND', (0,0), (-1,0), colors.grey),
            ('TEXTCOLOR', (0,0), (-1,0), colors.whitesmoke),
            ('ALIGN', (0,0), (-1,-1), 'CENTER'),
            ('FONTNAME', (0,0), (-1,-1), 'SimSun'),
            ('FONTSIZE', (0,0), (-1,0), 10),
            ('BOTTOMPADDING', (0,0), (-1,0), 12),
            ('BACKGROUND', (0,1), (-1,-1), colors.white),
            ('GRID', (0,0), (-1,-1), 1, colors.black)
        ]))
        
        elements = [title, table]
        doc.build(elements)
        
        output.seek(0)
        response = make_response(output.getvalue())
        response.headers['Content-Type'] = 'application/pdf'
        response.headers['Content-Disposition'] = f'attachment; filename=annual_emission_report_{selected_year}.pdf'
        return response
    
    elif format == 'word':
        from docx import Document
        from docx.shared import Inches, Pt
        from docx.enum.text import WD_ALIGN_PARAGRAPH
        
        output = BytesIO()
        doc = Document()
        
        title = doc.add_paragraph(f'{selected_year}年度温室气体排放量汇总表')
        title.alignment = WD_ALIGN_PARAGRAPH.CENTER
        title.runs[0].font.size = Pt(16)
        title.runs[0].bold = True
        title.runs[0].font.name = 'Microsoft YaHei'
        
        table = doc.add_table(rows=1, cols=3)
        table.style = 'Table Grid'
        
        hdr_cells = table.rows[0].cells
        hdr_cells[0].text = '源类别'
        hdr_cells[1].text = '排放量(t)'
        hdr_cells[2].text = '排放量(tCO2e)'
        
        # 添加数据行
        categories = [
            ('化石燃料燃烧CO2排放', 'fossil_fuel_t', 'fossil_fuel_tco2e'),
            ('CO2过程排放', 'process_co2_t', 'process_co2_tco2e'),
            ('HFCs过程排放', 'process_hfcs_t', 'process_hfcs_tco2e'),
            ('PFCs过程排放', 'process_pfcs_t', 'process_pfcs_tco2e'),
            ('SF6过程排放', 'process_sf6_t', 'process_sf6_tco2e'),
            ('购入电力产生的排放量', 'purchased_electricity_t', 'purchased_electricity_tco2e'),
            ('购入热力产生的排放量', 'purchased_heat_t', 'purchased_heat_tco2e'),
            ('输出电力产生的排放量', 'exported_electricity_t', 'exported_electricity_tco2e'),
            ('输出热力产生的排放量', 'exported_heat_t', 'exported_heat_tco2e'),
            ('企业温室气体排放总量(不包括购入和输出的电力、热力)', 'total_without_energy_t', 'total_without_energy_tco2e'),
            ('企业温室气体排放总量(包括购入和输出的电力、热力)', 'total_with_energy_t', 'total_with_energy_tco2e')
        ]
        
        for category, t_key, tco2e_key in categories:
            row = table.add_row().cells
            row[0].text = category
            row[1].text = str(report_data.get(t_key, ''))
            row[2].text = str(report_data.get(tco2e_key, ''))
        
        doc.save(output)
        output.seek(0)
        
        response = make_response(output.getvalue())
        response.headers['Content-Type'] = 'application/vnd.openxmlformats-officedocument.wordprocessingml.document'
        response.headers['Content-Disposition'] = f'attachment; filename=annual_emission_report_{selected_year}.docx'
        return response

